/**
 * 
 */
package com.quiz.dao;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import au.com.bytecode.opencsv.CSVReader;

import com.quiz.model.Player;
import com.quiz.model.Question;
import com.quiz.model.Rank;
import com.quiz.model.Section;


/**
 *
 * @author Brian Boyle
 * Apr 9, 2012
 */
public class MySQLiteHelper extends SQLiteOpenHelper {

    public static final String TABLE_QUESTION = "comments";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_COMMENT = "comment";

    private static final String DATABASE_NAME = "sopranosQuiz.db";
    private static final int DATABASE_VERSION = 2;
    private final Context context;

    /**
     * @param context
     * @param name
     * @param factory
     * @param version
     */
    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void onCreate(SQLiteDatabase database) {

        //Create Tables
        createPlayerTable(database);
        createSectionTable(database);
        createQuestionTable(database);

        //Insert Data
        loadSectionData(database);
        loadQuestionData(database);
        loadPlayerData(database);
    }

    private void loadPlayerData(SQLiteDatabase db) {
        ContentValues playerValues = new ContentValues();
        playerValues.put(Player.COLUMN_HIGHSCORE, 0);
        playerValues.put(Player.COLUMN_LIVES, 3);
        playerValues.put(Player.COLUMN_CURRENT_RANK, Rank.ASSOCIATE.getRankName());
        playerValues.put(Player.COLUMN_STATUS, Player.STATUS_ALIVE);

        db.insert(Player.TABLE_NAME, null, playerValues);
    }

    private void loadSectionData(SQLiteDatabase db) {
        try {
            CSVReader reader = new CSVReader(new InputStreamReader(context.getAssets().open("Sections.csv")));
            String[] nextLine;
            while ((nextLine = reader.readNext()) != null) {
                ContentValues sectionValues = new ContentValues();
                sectionValues.put(Section.COLUMN_NAME, nextLine[0]);
                sectionValues.put(Section.COLUMN_TOPSCORE, nextLine[1]);
                sectionValues.put(Section.COLUMN_STATUS, nextLine[2]);
                sectionValues.put(Section.COLUMN_SCORE_NEEDED, nextLine[3]);
                sectionValues.put(Section.COLUMN_DESCRIPTION, nextLine[4]);
                sectionValues.put(Section.COLUMN_IMAGE_NAME, nextLine[5]);
                sectionValues.put(Section.COLUMN_NUM_QUESTIONS, nextLine[6]);
                sectionValues.put(Section.COLUMN_POINTS_PER_QUESTION, nextLine[7]);
                sectionValues.put(Section.COLUMN_TIME_ALLOWED, nextLine[8]);
                sectionValues.put(Section.COLUMN_LIFE_BONUS_POINTS, nextLine[9]);
                sectionValues.put(Section.COLUMN_FIRST_GUESS_BONUS_POINTS, nextLine[10]);
                sectionValues.put(Section.COLUMN_SPEED_BONUS, nextLine[11]);

                //Insert the data
                db.insert(Section.TABLE_NAME, null, sectionValues);
            }
        } catch (FileNotFoundException e) {
            Log.v("FILE ERROR", "Could not find file Sections.csv");
        } catch (IOException e) {
            Log.v("READ ERROR", "Failed to read file Sections.csv");
        }
    }

    private void loadQuestionData(SQLiteDatabase db) {
        try {
            CSVReader reader = new CSVReader(new InputStreamReader(context.getAssets().open("SopranosQuestions.csv")));
            String[] nextLine;
            while ((nextLine = reader.readNext()) != null) {
                ContentValues questionValues = new ContentValues();
                questionValues.put(Question.COLUMN_QUESTION, nextLine[0]);
                questionValues.put(Question.COLUMN_LEVEL, nextLine[1]);
                questionValues.put(Question.COLUMN_ANSWER, nextLine[2]);
                questionValues.put(Question.COLUMN_POSSIBLE1, nextLine[3]);
                questionValues.put(Question.COLUMN_POSSIBLE2, nextLine[4]);
                questionValues.put(Question.COLUMN_POSSIBLE3, nextLine[5]);
                questionValues.put(Question.COLUMN_ASKED, nextLine[6]);

                //Insert the data
                db.insert(Question.TABLE_NAME, null, questionValues);
            }
        } catch (FileNotFoundException e) {
            Log.d("FILE ERROR", "Could not find file questions.csv");
        } catch (IOException e) {
            Log.d("READ ERROR", "Failed to read file questions.csv");
        }
    }

    private void createPlayerTable(SQLiteDatabase db) {
        String sql =
                "create table "
                        + Player.TABLE_NAME
                        + "( "
                        + Player.COLUMN_HIGHSCORE
                        + " integer default 0, "
                        + Player.COLUMN_LIVES
                        + " integer default 3, "
                        + Player.COLUMN_CURRENT_RANK
                        + " text default 'Associate', "
                        + Player.COLUMN_STATUS
                        + " text);";
        db.execSQL(sql);
    }

    private void createSectionTable(SQLiteDatabase db) {
        String sql =
                "create table "
                        + Section.TABLE_NAME
                        + "( "
                        + Section.COLUMN_ID
                        + " integer primary key autoincrement, "
                        + Section.COLUMN_NAME
                        + " text, "
                        + Section.COLUMN_TOPSCORE
                        + " integer, "
                        + Section.COLUMN_STATUS
                        + " text, "
                        + Section.COLUMN_SCORE_NEEDED
                        + " integer, "
                        + Section.COLUMN_DESCRIPTION
                        + " text, "
                        + Section.COLUMN_IMAGE_NAME
                        + " text,"
                        + Section.COLUMN_NUM_QUESTIONS
                        + " integer,"
                        + Section.COLUMN_POINTS_PER_QUESTION
                        + " integer,"
                        + Section.COLUMN_TIME_ALLOWED
                        + " integer,"
                        + Section.COLUMN_LIFE_BONUS_POINTS
                        + " integer,"
                        + Section.COLUMN_FIRST_GUESS_BONUS_POINTS
                        + " integer,"
                        + Section.COLUMN_SPEED_BONUS
                        + " integer);";

        db.execSQL(sql);
    }

    private void createQuestionTable(SQLiteDatabase db) {
        String sql =
                "create table "
                        + Question.TABLE_NAME
                        + "( "
                        + Question.COLUMN_ID
                        + " integer primary key autoincrement, "
                        + Question.COLUMN_QUESTION
                        + " text, "
                        + Question.COLUMN_ANSWER
                        + " text, "
                        + Question.COLUMN_POSSIBLE1
                        + " text, "
                        + Question.COLUMN_POSSIBLE2
                        + " text, "
                        + Question.COLUMN_POSSIBLE3
                        + " text, "
                        + Question.COLUMN_ASKED
                        + " int,"
                        + Question.COLUMN_LEVEL
                        + " int); ";
        db.execSQL(sql);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

}
